package com.teamit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.teamit.entity.Message;
import com.teamit.entity.MessageComment;
import com.teamit.util.db.DBHelp;
import com.teamit.util.db.Page;
import com.teamit.util.db.RowMapper;

public class MessageDao {
	private DBHelp<Message> dh=new DBHelp<Message>();
	private DBHelp<MessageComment> db=new DBHelp<MessageComment>();
	public int findAllByProject(String id,String employeeId,String time){
		String sql="SELECT count(*) FROM t_message WHERE project_id=? AND employee_id!=? AND createtime>?";
		return dh.executeQuery(sql,id,employeeId,time).intValue();
	}
	public void save(Message message){
		String sql="insert into t_message(id,content,employee_id,project_id) values(?,?,?,?)";
		dh.executeSQL(sql, message.getId(),message.getContent(),message.getEmployeeId(),message.getProjectId());
	}
	public List<Message> findByProjectId(String id,Page<Message> page){
		String sql="select id,content,createtime,employee_id,project_id where project_id=? limit ?,?";
		return dh.executeQueryToList(sql, new Mapper(), page.getStart(),page.getPageSize());
	}
	public int findCountById(String projectId) {
		String sql = "select count(*) from t_message where project_id=?";
		return dh.executeQuery(sql, projectId).intValue();
	}
	public Message findByIdMessge(String id){
		String sql="SELECT username,tm.id,tm.content,tm.createtime,tm.employee_id,tm.project_id FROM t_employee AS te,t_message AS tm WHERE te.id=tm.employee_id AND tm.id=?";
		return dh.executeQueryToObject(sql, new RowMapper<Message>(){
			public Message mapRow(ResultSet rs) throws SQLException {
				Message message=new Message();
				message.setId(rs.getString("tm.id"));
				message.setContent(rs.getString("tm.content"));
				message.setCreateTime(rs.getString("tm.createtime"));
				message.setEmployeeId(rs.getString("tm.employee_id"));
				message.setProjectId(rs.getString("tm.project_id"));
				message.setEmployeeName(rs.getString("username"));
				return message;
			}}, id);
	}
	public List<MessageComment> findMessage(){
		String sql="SELECT te.username,tm.employee_id,tm.project_id,tm.message_id,tm.`comment`,tm.createtime FROM t_messagecomment AS tm,t_employee AS te WHERE tm.employee_id=te.id";
		return db.executeQueryToList(sql, new RowMapper<MessageComment>(){
			public MessageComment mapRow(ResultSet rs) throws SQLException {
				MessageComment mc=new MessageComment();
				mc.setUsername(rs.getString("username"));
				mc.setEmployeeId(rs.getString("employee_id"));
				mc.setProjectId(rs.getString("project_id"));
				mc.setMessageId(rs.getString("message_id"));
				mc.setCreateTime(rs.getString("createtime"));
				mc.setComment(rs.getString("comment"));
				return mc;
			}});
	}
	public Page<Message> findById(String projectId,int pageNum){
		Page<Message> page = new Page<Message>(findCountById(projectId),pageNum);
		String sql = "SELECT username,tm.id,tm.content,tm.createtime,tm.employee_id,tm.project_id FROM t_employee AS te,t_message AS tm WHERE te.id=tm.employee_id AND tm.project_id=? order by createtime desc limit ?,?";
		List<Message> list = dh.executeQueryToList(sql, new Mapper(),projectId,page.getStart(),page.getPageSize());
		page.setList(list);
		return page;
	}
	private class Mapper implements RowMapper<Message>{
		public Message mapRow(ResultSet rs) throws SQLException {
			Message message=new Message();
			message.setId(rs.getString("id"));
			message.setContent(rs.getString("content"));
			message.setCreateTime(rs.getString("createtime"));
			message.setEmployeeId(rs.getString("employee_id"));
			message.setProjectId(rs.getString("project_id"));
			message.setEmployeeName(rs.getString("username"));
			return message;
		}}
}
